@echo off

call cfg.cmd

set date_start=%date%
set time_start=%time:~0,8%

rem check for variables
if [%SERVER%]==[] goto help
if [%UID%]==[] goto help
rem if [%PWD%]==[] goto help ; the password can be null
if [%DATABASE%]==[] goto help
if [%OUTPUT%]==[] goto help

rem create output directory if not exists
if not exist %OUTPUT% md %OUTPUT%
if not exist %OUTPUT% goto outputerror

md %OUTPUT%\bin>nul
copy bin\cisql.exe %OUTPUT%\bin>nul


if [%UNLOAD%]==[D] goto unloaddata

echo Unloading defaults...
%ISQL% -x -isql\def.sql -o%OUTPUT%\defaults.sql
if errorlevel 1 goto error


echo Unloading datatypes...
%ISQL% -x -isql\type.sql -o%OUTPUT%\datatype.sql
if errorlevel 1 goto error


echo Unloading table structure...
%ISQL% -x -isql\table.sql -o%OUTPUT%\tables.sql
if errorlevel 1 goto error

echo Unloading views...
%ISQL% -x -isql\view.sql -o%OUTPUT%\view.sql
if errorlevel 1 goto error



echo Unloading procedures...
%ISQL% -x -isql\proc.sql -o%OUTPUT%\procedures.tmp
if errorlevel 1 goto error
bin\procpp.exe %OUTPUT%\procedures.tmp %OUTPUT%\procedures.sql
if errorlevel 1 goto error
del  %OUTPUT%\procedures.tmp



echo Unloading foreign keys...
%ISQL% -x -isql\fkey.sql -o%OUTPUT%\fkeys.sql
if errorlevel 1 goto error



echo Unloading index...
%ISQL% -x -isql\index.sql -o%OUTPUT%\index.sql
if errorlevel 1 goto error



echo Unloading grant permission...
%ISQL% -x -isql\grant.sql -o%OUTPUT%\grant.sql
if errorlevel 1 goto error



:unloaddata
if [%UNLOAD%]==[S] goto unloaddataend


echo Creating script for data unloading...
%ISQL% -x -isql\unload_data.sql -o%OUTPUT%\unload_data.cmd
if errorlevel 1 goto error
echo Creating script for data loading...
%ISQL% -x -isql\load_data.sql -o%OUTPUT%\load_data.cmd
if errorlevel 1 goto error


echo Unloading data...
md %OUTPUT%\data>nul
call %OUTPUT%\unload_data.cmd
rem del %OUTPUT%\unload_data.cmd

:unloaddataend


rem !!!!!!!!! CREATE LOAD SCRIPT !!!!!!!!!!


if [%UNLOAD%]==[D] goto loaddata


rem create cfg.cmd
echo set SERVER=>%OUTPUT%\cfg.cmd
echo set UID=%UID%>>%OUTPUT%\cfg.cmd
echo set PWD=%PWD%>>%OUTPUT%\cfg.cmd
echo set DATABASE=%DATABASE%>>%OUTPUT%\cfg.cmd
echo rem Command line for isql>>%OUTPUT%\cfg.cmd
echo set ISQL=bin\cisql -S%%SERVER%% -U%%UID%% -P%%PWD%% -D%%DATABASE%%>>%OUTPUT%\cfg.cmd

rem create !.cmd
echo @echo off>%OUTPUT%\!.cmd
echo rem Command file for load database>>%OUTPUT%\!.cmd
echo rem Settings:>>%OUTPUT%\!.cmd
echo call cfg.cmd>>%OUTPUT%\!.cmd

echo if exist error.log del error.log>>%OUTPUT%\!.cmd

copy /Y sql\create_drop_all.sql %OUTPUT%
echo echo ARE YOU SURE YOU WANT TO DROP EVERYTHING FROM>>%OUTPUT%\!.cmd
echo echo %%SERVER%%.%%DATABASE%%>>%OUTPUT%\!.cmd
echo echo IF NO, PRESS CTRL+C>>%OUTPUT%\!.cmd
echo pause>>%OUTPUT%\!.cmd

echo set date_start=%%date%%>>%OUTPUT%\!.cmd
echo set time_start=%%time:~0,8%%>>%OUTPUT%\!.cmd

echo echo Prepare drop_all.sql script...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -icreate_drop_all.sql -odrop_all.sql>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd
echo echo Execute drop_all.sql script...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -idrop_all.sql>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd




echo echo Creating defaults...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -idefaults.sql -onul>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd

echo echo Creating datatypes...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -idatatype.sql -onul>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd


echo echo Creating tables...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -itables.sql -onul>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd

echo echo Creating views...>>%OUTPUT%\!.cmd
echo %%ISQL%% -x -iview.sql -onul>>%OUTPUT%\!.cmd
echo if errorlevel 1 goto error>>%OUTPUT%\!.cmd

:loaddata
if [%UNLOAD%]==[S] goto loaddataend
echo echo Loading data...>>%OUTPUT%\!.cmd
echo call load_data.cmd>>%OUTPUT%\!.cmd
:loaddataend

if [%UNLOAD%]==[D] goto :endstruct

echo echo Creating foreign keys...>>%OUTPUT%\!.cmd
echo %%ISQL%% -ifkeys.sql -onul>>%OUTPUT%\!.cmd

echo echo Creating indexes...>>%OUTPUT%\!.cmd
echo %%ISQL%% -iindex.sql -onul>>%OUTPUT%\!.cmd

echo echo Creating procedures...>>%OUTPUT%\!.cmd
echo %%ISQL%% -iprocedures.sql -onul>>%OUTPUT%\!.cmd
echo echo It can produce some errors because of dependency.>>%OUTPUT%\!.cmd
echo echo If so, then execute load_proc.cmd to be sure.>>%OUTPUT%\!.cmd

rem create load_proc.cmd for separate procedure loading
echo @echo off>%OUTPUT%\load_proc.cmd
echo call cfg.cmd>>%OUTPUT%\load_proc.cmd
echo %%ISQL%% -iprocedures.sql -onul>>%OUTPUT%\load_proc.cmd

echo echo Creating grant permission...>>%OUTPUT%\!.cmd
echo %%ISQL%% -igrant.sql -onul>>%OUTPUT%\!.cmd

:endstruct

echo echo Ok.>>%OUTPUT%\!.cmd
echo goto end>>%OUTPUT%\!.cmd
echo :error>>%OUTPUT%\!.cmd
echo echo Error.>>%OUTPUT%\!.cmd
echo :end>>%OUTPUT%\!.cmd
echo set date_finish=%%date%%>>%OUTPUT%\!.cmd
echo set time_finish=%%time:~0,8%%>>%OUTPUT%\!.cmd

echo echo Start : %%date_start%%  %%time_start%%>>%OUTPUT%\!.cmd
echo echo Finish: %%date_finish%%  %%time_finish%%>>%OUTPUT%\!.cmd


echo Ok.
goto end
:outputerror
echo Error: Output directory "%OUTPUT%" can't be created
:help
echo Please set the following variables in cfg.cmd file
echo SERVER		Sybase sql server alias (sql.ini file)
echo UID		login name
echo PWD		login password
echo DATABASE	database
echo OUTPUT		output directory

goto end
:error
echo Error.
:end
set date_finish=%date%
set time_finish=%time:~0,8%

echo Start : %date_start%  %time_start%
echo Finish: %date_finish%  %time_finish%
